package cn.edu.scujj.dao;
import cn.edu.scujj.bean.Breakage;
import cn.edu.scujj.utils.DButil;
import java.sql.Connection;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import java.time.format.DateTimeFormatter;
import java.sql.Timestamp;

public class BreakageDao {

    public List< Breakage> findAll()
    {
        Connection conn = DButil.getConnection();
        String sql = "select * from librarysystem.breakage_info";
        Statement stmt = null;
        List<Breakage> list = new ArrayList<>();
        try {
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Breakage breakage = new Breakage();
                breakage.setBreakageId(rs.getInt("breakage_id"));
                breakage.setBookId(rs.getInt("book_id"));
                breakage.setBreakageTime(rs.getString("breakage_time"));
                breakage.setBreakageSum(rs.getInt("breakage_sum"));
                list.add(breakage);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButil.close(conn);
        }
        return list;
    }
    // 类别添加
    public boolean addBreakage(Breakage breakage) {
        Connection conn = DButil.getConnection();
        PreparedStatement pstmt = null;
        PreparedStatement updatePstmt = null;
        ResultSet rs = null;

        String dateStr = breakage.getBreakageTime().trim();

        if (breakage.getBreakageTime() == null || dateStr.isEmpty()) {
            throw new IllegalArgumentException("损坏时间不能为空");
        }

        try {
            conn.setAutoCommit(false); // 开启事务

            // 检查ID是否已存在
            pstmt = conn.prepareStatement("SELECT 1 FROM librarysystem.breakage_info WHERE breakage_id = ?");
            pstmt.setInt(1, breakage.getBreakageId());
            rs = pstmt.executeQuery();
            if (rs.next()) return false;

            // 校验 book_id 是否存在
            pstmt = conn.prepareStatement("SELECT 1 FROM book_info WHERE book_id = ?");
            pstmt.setInt(1, breakage.getBookId());
            rs = pstmt.executeQuery();
            if (!rs.next()) {
                throw new IllegalArgumentException("图书ID不存在");
            }

            LocalDate localDate = null;
            DateTimeFormatter[] formatters = {
                    DateTimeFormatter.ofPattern("yyyy-MM-dd"),
                    DateTimeFormatter.ofPattern("yyyy/M/d"),
                    DateTimeFormatter.ofPattern("yyyy-MM-d"),
                    DateTimeFormatter.ofPattern("yyyy-M-d")
            };

            for (DateTimeFormatter formatter : formatters) {
                try {
                    localDate = LocalDate.parse(dateStr, formatter);
                    break;
                } catch (Exception ignored) {}
            }

            if (localDate == null) {
                throw new IllegalArgumentException("时间格式错误，请使用类似 2025-06-06 的格式");
            }
            // 插入损坏记录
            pstmt = conn.prepareStatement("INSERT INTO librarysystem.breakage_info (breakage_id, book_id, breakage_time, breakage_sum) VALUES (?, ?, ?, ?)");
            pstmt.setInt(1, breakage.getBreakageId());
            pstmt.setInt(2, breakage.getBookId());
            pstmt.setDate(3, java.sql.Date.valueOf(localDate));

            pstmt.setInt(4, breakage.getBreakageSum());
            pstmt.executeUpdate();

            updatePstmt = conn.prepareStatement("UPDATE book_info SET total_num = COALESCE(total_num, 0) - ? WHERE book_id = ?");
            updatePstmt.setInt(1, breakage.getBreakageSum());
            updatePstmt.setInt(2, breakage.getBookId());
            int rowsAffected = updatePstmt.executeUpdate();

            if (rowsAffected == 0) {
                throw new RuntimeException("更新库存失败，可能图书ID不存在或库存不足");
            }


            conn.commit(); // 提交事务
            return true;

        } catch (SQLException | IllegalArgumentException e) {
            try {
                if (conn != null) conn.rollback(); // 回滚事务
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            throw new RuntimeException("操作失败: " + e.getMessage(), e);
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                if (updatePstmt != null) updatePstmt.close();
                if (rs != null) rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DButil.close(conn);
        }
    }

    // 查询
    public List<Breakage> queryBreakages(Object condition, boolean isById) {
        List<Breakage> breakages = new ArrayList<>();
        Connection conn = DButil.getConnection();
        String sql = isById ?
                "SELECT * FROM librarysystem.breakage_info WHERE book_id = ?" :
                "SELECT * FROM librarysystem.breakage_info WHERE DATE(breakage_time) = ?";

        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            pstmt = conn.prepareStatement(sql);

            if (isById) {
                if (condition instanceof Integer) {
                    pstmt.setInt(1, (Integer) condition);
                } else if (condition instanceof String) {
                    pstmt.setInt(1, Integer.parseInt((String) condition));
                } else {
                    throw new IllegalArgumentException("book_id 必须为数字");
                }
            } else {
                LocalDate date;
                if (condition instanceof LocalDate) {
                    date = (LocalDate) condition;
                } else if (condition instanceof String) {
                    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                    date = LocalDate.parse((String) condition, formatter);
                } else {
                    throw new IllegalArgumentException("时间格式错误，请使用 yyyy-MM-dd 格式的字符串");
                }
                pstmt.setDate(1, java.sql.Date.valueOf(date));
            }

            rs = pstmt.executeQuery();

            System.out.println("执行的SQL: " + pstmt.toString());

            while (rs.next()) {
                Breakage breakage = new Breakage();
                breakage.setBreakageId(rs.getInt("breakage_id"));
                breakage.setBookId(rs.getInt("book_id"));
                Timestamp timestamp = rs.getTimestamp("breakage_time");
                if (timestamp != null) {
                    breakage.setBreakageTime(timestamp.toLocalDateTime().format(DateTimeFormatter.ISO_DATE));
                } else {
                    breakage.setBreakageTime(null);
                }

                breakage.setBreakageSum(rs.getInt("breakage_sum"));

                breakages.add(breakage);
            }

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("查询失败，错误信息：" + e.getMessage());
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DButil.close(conn);
        }
        return breakages;
    }

    // 类别修改
    public boolean updateBreakage(Breakage breakage) {
        Connection conn = DButil.getConnection();
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;
        PreparedStatement pstmt3 = null;

        try {
            conn.setAutoCommit(false); // 开启事务

            // 1. 更新 breakage_info 表中的 breakage_sum
            String sql1 = "UPDATE breakage_info SET breakage_sum = ? WHERE breakage_id = ?";
            pstmt1 = conn.prepareStatement(sql1);
            pstmt1.setInt(1, breakage.getBreakageSum());
            pstmt1.setInt(2, breakage.getBreakageId());
            int result1 = pstmt1.executeUpdate();

            if (result1 > 0) {
                // 2. 检查 book_info 表中是否存在对应的 book_id
                String checkSql = "SELECT 1 FROM book_info WHERE book_id = ?";
                pstmt3 = conn.prepareStatement(checkSql);
                pstmt3.setInt(1, breakage.getBookId());

                ResultSet rs = pstmt3.executeQuery();
                if (rs.next()) {
                    // 获取当前 total_num
                    String getNumSql = "SELECT total_num FROM book_info WHERE book_id = ?";
                    PreparedStatement pstmtGetNum = conn.prepareStatement(getNumSql);
                    pstmtGetNum.setInt(1, breakage.getBookId());
                    ResultSet numRs = pstmtGetNum.executeQuery();

                    if (numRs.next()) {
                        int currentTotalNum = numRs.getInt("total_num");

                        if (currentTotalNum <= 0) {
                            throw new RuntimeException("库存不足，无法继续扣除");
                        }

                        // 更新 total_num
                        String updateTotalNumSql = "UPDATE book_info SET total_num = ? WHERE book_id = ?";
                        pstmt2 = conn.prepareStatement(updateTotalNumSql);
                        pstmt2.setInt(1, currentTotalNum - 1);
                        pstmt2.executeUpdate();
                    }
                }
            }

            conn.commit(); // 提交事务
            return result1 > 0;

        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback(); // 回滚事务
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            throw new RuntimeException("操作失败: " + e.getMessage(), e);
        } finally {
            // 关闭资源（PreparedStatement）
            try {
                if (pstmt1 != null) pstmt1.close();
                if (pstmt2 != null) pstmt2.close();
                if (pstmt3 != null) pstmt3.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DButil.close(conn);
        }
    }

    // 类别删除
    public boolean deleteBreakage(int breakageId) {
        Connection conn = DButil.getConnection();
        PreparedStatement pstmt = null;
        try {
            conn.setAutoCommit(false); // 开启事务

            // 删除 breakage_info 表中的记录
            String sql = "DELETE FROM breakage_info WHERE breakage_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, breakageId);  // 直接使用传入的 breakageId
            int result = pstmt.executeUpdate();
            conn.commit();

            return result > 0; // 返回是否成功
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DButil.close(conn);
        }
    }


}
